#!/usr/bin/env perl
use strict;

# use CGI;
use DBI;
use Geo::IP;

# my ($page);
# Database connection.
my ($dsn)       = "DBI:mysql:database=eprintstats;host=127.0.0.1";
my ($user_name) = "eprintstatspub";
my ($password)  = "public";
my ( $connect, $query, %types, %unmapped, $stat, $row, $num_rows, $vtype );

# Geolocation database.
my ($gi);
my ($gidb) = "/usr/local/share/GeoIP/GeoLiteCity.dat";

# Miscellaneous variable.
my ( %cities, %IPs );
my ($num_entries) = -1;
my ($num_hits)    = 0;
my ( $ip,  $count, $location );
my ( $lat, $long,  $city, $key, $country ) = ( 0, 0, '', '', '' );

# $page = new CGI;
# print $page->header( -type => "text/xml", -Pragma => 'no-cache' );
$num_entries = -1;                             #$page->param('top');
$gi = Geo::IP->open( $gidb, GEOIP_STANDARD )
  or die "Unable to open GeoIP database $gidb\n";
$connect = DBI->connect( $dsn, $user_name, $password, { RaiseError => 1 } );
$types{'download'}    = $types{'abstract'}    = 0;
$unmapped{'download'} = $unmapped{'abstract'} = 0;
$query = "SELECT ip, view_type, country_name, COUNT(*) AS count
	 FROM view
	 WHERE archive_name = 'cardrona'
	   AND country_name = 'Brazil'
	 GROUP BY ip, view_type, country_name
	 ORDER BY count DESC" . ( ( $num_entries > 0 ) ? " LIMIT $num_entries" : '' );
$stat = $connect->prepare($query);
$stat->execute();
$num_rows = $stat->rows;
print 'IP' . "\t\t\t\t\t" . 'LONGITUDE' . "\t\t" . 'LATITUDE' . "\t\t" . 'CITY'
  . "\t\t\t\tCOUNTRY\n";
print "-------------------------------------------------------------------------------\n";

if ( $num_rows > 0 ) {
	$num_entries = $num_rows if ( $num_entries < 1 );
	while ( $row = $stat->fetchrow_hashref() ) {
		$ip       = $row->{'ip'};
		$count    = $row->{'count'};
		$vtype    = $row->{'view_type'};
		$country  = $row->{'country_name'};
		$IPs{$ip} = 1;
		$location = $gi->record_by_name($ip);
		if ( defined($location) ) {
			$lat  = $location->latitude;
			$long = $location->longitude;
			$city = (
					  ( $location->city eq '' )
					  ? 'Unknown'
					  : $location->city
			);
			$key = $city . " ($lat, $long)";
			if ( !defined( $cities{$key} ) ) {
				$cities{$key}{'name'}     = $city;
				$cities{$key}{'lat'}      = $lat;
				$cities{$key}{'long'}     = $long;
				$cities{$key}{'abstract'} = 0;
				$cities{$key}{'download'} = 0;
			}
			$cities{$key}{$vtype} += $count;
			$types{$vtype}        += $count;
		} else {
			$unmapped{$vtype} += $count;
		}  
		#$lat = sprintf('%xx.yyyyf', $lat);  
		#$city = sprintf('%20s', $city);
		print sprintf("%12s\t\t%8s\t\t%8s\t\t%-15s\t\t%-10s\n", $ip, $long, $lat, $city, $country);
#		print $ip . "\t\t" . $long . "\t\t" . $lat . "\t\t\t" . $city
#		  .  "\t\t\t"
#		  . $country . "\n";
	}
}
$stat->finish();
$connect->disconnect();

sub round {
	my ($n) = shift;
	return int( $n + 0.5 * ( $n <=> 0 ) );
}